﻿Public Class Gestion_Cd
    Enum estado
        inicial
        insertar
        modificar
    End Enum
    Dim operacion As estado = estado.inicial
    Dim nombre_tabla As String = "cd"
    Dim tratamiento As New Tratamiento_Objetos
    Dim datos_clase As New datos

    Dim generos As New AutoCompleteStringCollection
    Dim sellos As New AutoCompleteStringCollection

    'Private Sub soloLetras(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_apellido.KeyPress
    '    If Not Char.IsLetter(e.KeyChar) And Not Char.IsWhiteSpace(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
    '        e.KeyChar = ""
    '    End If
    'End Sub

    Private Sub soloNumeros(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_codigo.KeyPress, txt_precio.KeyPress, txt_año.KeyPress
        If Not Char.IsNumber(e.KeyChar) And Not Char.IsControl(e.KeyChar) Then
            e.KeyChar = ""
        End If
    End Sub

    Private Sub botones(ByVal flag As Boolean)
        Me.btn_nuevo.Enabled = flag
        Me.btn_salir.Enabled = flag
        Me.btn_cancelar.Enabled = Not flag
        Me.btn_guardar.Enabled = Not flag
        Me.btn_eliminar.Enabled = Not flag
        Me.btn_modificar.Enabled = Not flag
    End Sub

    Private Function validar() As Boolean
        Dim flag As Boolean = True
        If cmb_sello.SelectedIndex = -1 Then
            lbl_sello.ForeColor = Color.Red
            cmb_sello.Focus()
            flag = False
        Else
            lbl_sello.ForeColor = Color.Black
        End If
        If cmb_genero.SelectedIndex = -1 Then
            lbl_genero.ForeColor = Color.Red
            cmb_genero.Focus()
            flag = False
        Else
            lbl_genero.ForeColor = Color.Black
        End If

        If String.IsNullOrEmpty(Me.txt_año.Text) Then
            lbl_año.ForeColor = Color.Red
            txt_año.Focus()
            flag = False
        Else
            lbl_año.ForeColor = Color.Black
        End If
        If String.IsNullOrEmpty(Me.txt_precio.Text) Then
            lbl_precio.ForeColor = Color.Red
            txt_precio.Focus()
            flag = False
        Else
            lbl_precio.ForeColor = Color.Black
        End If
        If String.IsNullOrEmpty(Me.txt_titulo.Text) Then
            lbl_titulo.ForeColor = Color.Red
            txt_titulo.Focus()
            flag = False
        Else
            lbl_titulo.ForeColor = Color.Black
        End If
        If String.IsNullOrEmpty(Me.txt_codigo.Text) Then
            lbl_codigo.ForeColor = Color.Red
            txt_codigo.Focus()
            flag = False
        Else
            lbl_codigo.ForeColor = Color.Black
        End If
        Return flag
    End Function
    Private Function validarUso() As Boolean
        'preguntar en las 3 tablas si alguno lo usa.

        'Dim condicion As String = "codigo_cd = " & txt_codigo.Text
        'Dim datos As Data.DataTable = datos_clase.leo_tabla("factura", condicion)
        Return False

        'If datos.Rows.Count = 0 Then
        '    Return False
        'Else
        '    Return True
        'End If
    End Function

    Private Sub Gestion_Cd_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        botones(True)
        cargarGeneros()
        cargarSellos()
        cargarCd()
        tratamiento.habilitar(False, Me.Controls)
        btn_nuevo.Focus()
        tratamiento.limpiar(Me.Controls)

        cmb_genero.AutoCompleteMode = AutoCompleteMode.Suggest
        cmb_genero.AutoCompleteSource = AutoCompleteSource.CustomSource
        cmb_genero.AutoCompleteCustomSource = generos

        cmb_sello.AutoCompleteMode = AutoCompleteMode.Suggest
        cmb_sello.AutoCompleteSource = AutoCompleteSource.CustomSource
        cmb_sello.AutoCompleteCustomSource = sellos
    End Sub

    Private Sub cargarGeneros()
        Dim tabla As Data.DataTable = datos_clase.leo_tabla("genero")
        cmb_genero.Items.Clear()
        cmb_genero.DataSource = tabla
        cmb_genero.ValueMember = "id_genero"
        cmb_genero.DisplayMember = "nombre"
        Dim fila As Integer = 0
        For fila = 0 To tabla.Rows.Count - 1
            generos.Add(tabla.Rows(fila)(1))
        Next
    End Sub

    Private Sub cargarSellos()
        Dim tabla As Data.DataTable = datos_clase.leo_tabla("sello_discografico")
        cmb_sello.Items.Clear()
        cmb_sello.DataSource = tabla
        cmb_sello.ValueMember = "id_sello_discografico"
        cmb_sello.DisplayMember = "nombre"
        Dim fila As Integer = 0
        For fila = 0 To tabla.Rows.Count - 1
            sellos.Add(tabla.Rows(fila)(1))
        Next
    End Sub

    Private Sub cargarCd()
        Dim conexion As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        Dim data As New Data.DataTable
        conexion.ConnectionString = datos_clase._cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        Dim sql As String = "SELECT cd.codigo_cd , cd.titulo, cd.precio_venta, genero.nombre FROM (cd INNER JOIN genero on cd.id_genero = genero.id_genero)"
        cmd.CommandText = sql
        data.Load(cmd.ExecuteReader())
        conexion.Close()
        Dim fila As Integer = 0

        DataGridView1.Rows.Clear()

        For fila = 0 To data.Rows.Count - 1
            DataGridView1.Rows.Add()
            DataGridView1.Rows(fila).Cells(0).Value = data.Rows(fila)(0)
            DataGridView1.Rows(fila).Cells(1).Value = data.Rows(fila)(1)
            DataGridView1.Rows(fila).Cells(2).Value = data.Rows(fila)(2)
            DataGridView1.Rows(fila).Cells(3).Value = data.Rows(fila)(3)
        Next
    End Sub


    Private Sub btn_nuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nuevo.Click
        operacion = estado.insertar
        botones(False)
        Me.btn_eliminar.Enabled = False
        Me.btn_modificar.Enabled = False
        tratamiento.habilitar(True, Me.Controls)
        Me.txt_codigo.Focus()
    End Sub

    Private Sub btn_guardar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_guardar.Click
        If validar() = True Then
            If validarExistencia() = False Then
                If MessageBox.Show("¿Desea guardar el " & nombre_tabla & "?", "Confirmacion", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                    If guardar() = 1 Then
                        MessageBox.Show("Carga realizada con exito.", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        cargarCd()
                        operacion = estado.inicial
                    Else
                        MessageBox.Show("Error al realizar la carga.", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End If
                    tratamiento.limpiar(Me.Controls)
                    tratamiento.habilitar(False, Me.Controls)
                    botones(True)
                    btn_nuevo.Focus()
                End If
            Else
                MessageBox.Show("Codigo ya en uso.", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txt_codigo.Focus()
            End If
        End If
    End Sub

    Private Sub btn_modificar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_modificar.Click
        If validar() = True Then
            If MessageBox.Show("¿Desea modificar el " & nombre_tabla & "?", "Consulta", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                Dim conexion As New SqlClient.SqlConnection
                Dim cmd As New SqlClient.SqlCommand
                Dim consulta As String = ""
                conexion.ConnectionString = datos_clase._cadena
                conexion.Open()
                consulta = "update " & nombre_tabla
                consulta &= " set titulo = '" & Me.txt_titulo.Text & "',"
                consulta &= " precio_venta = '" & Me.txt_precio.Text & "',"
                consulta &= " año_publicacion = '" & Me.txt_año.Text & "',"
                consulta &= " id_genero = '" & Me.cmb_genero.SelectedValue & "',"
                consulta &= " id_sello_discografico = '" & Me.cmb_sello.SelectedValue & "'"
                consulta &= " where codigo_cd = '" & Me.txt_codigo.Text & "'"

                cmd.CommandType = CommandType.Text
                cmd.CommandText = consulta
                cmd.Connection = conexion
                Dim filas As Integer = cmd.ExecuteNonQuery()
                conexion.Close()

                If filas = 1 Then
                    MessageBox.Show("Modificacion realizada.", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    operacion = estado.inicial
                Else
                    MessageBox.Show("Error al modificar", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    operacion = estado.inicial
                End If
                cargarCd()
                tratamiento.limpiar(Me.Controls)
                tratamiento.habilitar(False, Me.Controls)
                operacion = estado.inicial
                botones(True)
            End If
        End If
    End Sub
    Private Sub btn_eliminar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_eliminar.Click
        If MessageBox.Show("¿Desea eliminar el " & nombre_tabla & "?", "Pregunta", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            If validarUso() = False Then
                Dim conexion As New SqlClient.SqlConnection
                Dim cmd As New SqlClient.SqlCommand
                Dim consulta As String = "delete from " & nombre_tabla & " where codigo_cd = '" & Me.txt_codigo.Text & "'"
                conexion.ConnectionString = datos_clase._cadena
                conexion.Open()
                cmd.Connection = conexion
                cmd.CommandType = CommandType.Text
                cmd.CommandText = consulta
                Dim filas As Integer = cmd.ExecuteNonQuery()
                conexion.Close()
                If filas = 1 Then
                    MessageBox.Show("Eliminacion realizada.", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    cargarCd()
                    operacion = estado.inicial
                Else
                    MessageBox.Show("Error al eliminar", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    operacion = estado.inicial
                End If
                tratamiento.limpiar(Me.Controls)
                tratamiento.habilitar(False, Me.Controls)
                botones(True)
            Else
                MessageBox.Show("Existen facturas o temas usando este " & nombre_tabla & ". Elimine las facturas o los temas.", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End If
    End Sub
    Private Sub btn_salir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_salir.Click
        Close()
    End Sub

    Private Sub DataGridView1_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
        Me.operacion = estado.modificar
        Dim condicion As String = "codigo_cd = '" & DataGridView1.CurrentRow.Cells(0).Value & "'"
        Dim tabla As Data.DataTable = datos_clase.leo_tabla("cd", condicion)

        Me.txt_codigo.Text = tabla.Rows(0)(0)
        Me.txt_titulo.Text = tabla.Rows(0)(1)
        Me.txt_precio.Text = tabla.Rows(0)(2)
        Me.cmb_genero.SelectedValue = tabla.Rows(0)(3)
        Me.txt_año.Text = tabla.Rows(0)(4)
        Me.cmb_sello.SelectedValue = tabla.Rows(0)(5)

        tratamiento.habilitar(True, Me.Controls)
        tratamiento.labels_Color(Color.Black, Me.Controls)
        txt_codigo.Enabled = False
        botones(False)
        Me.btn_guardar.Enabled = False
    End Sub

    Private Sub Gestion_Cliente_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        If operacion = estado.inicial Then
            If MessageBox.Show("¿Desea salir?", "Alerta", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then
                e.Cancel = True
            End If
        Else
            MessageBox.Show("Termine la operacion actual!", "Notificacion", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            e.Cancel = True
        End If
    End Sub


    Private Function guardar() As Integer
        Dim conexion As New SqlClient.SqlConnection
        Dim cmd As New SqlClient.SqlCommand
        conexion.ConnectionString = datos_clase._cadena
        conexion.Open()
        cmd.Connection = conexion
        cmd.CommandType = CommandType.Text
        Dim sql As String = "insert into " & nombre_tabla & "(codigo_cd,titulo,precio_venta,id_genero,año_publicacion,id_sello_discografico) values ('"
        sql &= txt_codigo.Text & "','" & txt_titulo.Text & "','" & txt_precio.Text & "','" & cmb_genero.SelectedValue & "','" & txt_año.Text & "','"
        sql &= cmb_sello.SelectedValue & "')"
        cmd.CommandText = sql
        Dim temporal As Integer = cmd.ExecuteNonQuery
        conexion.Close()
        Return temporal
    End Function


    Private Sub btn_cancelar_Click_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_cancelar.Click
        If operacion = estado.insertar Then
            If MessageBox.Show("¿Desea cancelar la carga?", "Confirmacion", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                tratamiento.limpiar(Me.Controls)
                tratamiento.habilitar(False, Me.Controls)
                tratamiento.labels_Color(Color.Black, Me.Controls)
                Me.btn_nuevo.Focus()
                botones(True)
                operacion = estado.inicial
            End If
        Else
            If MessageBox.Show("¿Desea cancelar la modificacion?", "Confirmacion", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                tratamiento.limpiar(Me.Controls)
                tratamiento.habilitar(False, Me.Controls)
                tratamiento.labels_Color(Color.Black, Me.Controls)
                Me.btn_nuevo.Focus()
                botones(True)
                operacion = estado.inicial
            End If
        End If
    End Sub

    Private Function validarExistencia()
        Dim condicion As String = "codigo_cd = " & txt_codigo.Text
        Dim datos As Data.DataTable = datos_clase.leo_tabla("cd", condicion)
        If datos.Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function

End Class